package com.xdwanj.dao.impl;

import com.mysql.jdbc.StringUtils;
import com.xdwanj.dao.BaseDao;
import com.xdwanj.dao.EmpDao;
import com.xdwanj.pojo.Emp;
import com.xdwanj.pojo.Role;
import com.xdwanj.util.PageBean;
import com.xdwanj.util.UtilString;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class EmpDaoImpl implements EmpDao {

    BaseDao baseDao = new BaseDao();

    JdbcTemplate jdbcTemplate = baseDao.getJdbcTemplate();


    @Override
    public Emp findEmpById(int eid) throws Exception {
        Emp emp = null;
        List<Role> roleList = null;
        Set<Role> roleSet = null;
        String querySql = " from emp e,\n" +
                "     emp_role er,\n" +
                "     role r\n" +
                "where r.id = er.r_id\n" +
                "  and e.id = er.e_id\n" +
                "  and e.id = ? ";

        emp = jdbcTemplate.queryForObject("select *, e.id id " + querySql + " limit 1 offset 0 ", new BeanPropertyRowMapper<Emp>(Emp.class), eid);

        roleList = jdbcTemplate.query(" select *, r.id id, e.id empId " + querySql, new BeanPropertyRowMapper<Role>(Role.class), eid);

        roleSet = new HashSet<>(roleList);

        emp.setRoleList(new ArrayList<Role>(roleSet));

        return emp;
    }

    @Override
    public int updateEmp(Emp emp) throws Exception {
        int updateRow = 0;
        String updateEmpSql = " update emp\n" +
                "set name      = ?,\n" +
                "    sex       = ?,\n" +
                "    birthday  = ?,\n" +
                "    age       = ?,\n" +
                "    salary    = ?,\n" +
                "    phone_num = ?,\n" +
                "    email     = ?\n" +
                "where id = ? ";

        updateRow = jdbcTemplate.update(updateEmpSql,
                emp.getName(),
                emp.getSex(),
                emp.getBirthday(),
                emp.getAge(),
                emp.getSalary(),
                emp.getPhoneNum(),
                emp.getEmail(),
                emp.getId());

        return updateRow;
    }

    @Override
    public int deleteEmp(int eid) throws Exception {
        int delRow = 0;
        String delEmpSql = " delete from emp where id = ? ";
        String delEmpAndRoleSql = " delete from emp_role where e_id = ? ";

        delRow += jdbcTemplate.update(delEmpSql, eid);
        delRow += jdbcTemplate.update(delEmpAndRoleSql, eid);

        return delRow;
    }

    @Override
    public Emp addEmp(Emp emp, List<Integer> ridList) throws Exception {
        String updateEmpSql = " insert into emp(name, sex, birthday, age, salary, phone_num, email) values (?, ?, ?, ?, ?, ?, ?) ";
        String querySql = " select * from emp where phone_num = ? ";
        Emp empTable = null;

        jdbcTemplate.update(updateEmpSql,
                emp.getName(),
                emp.getSex(),
                emp.getBirthday(),
                emp.getAge(),
                emp.getSalary(),
                emp.getPhoneNum(),
                emp.getEmail());

        empTable = jdbcTemplate.queryForObject(querySql, new BeanPropertyRowMapper<Emp>(Emp.class), emp.getPhoneNum());

        insertEmpAndRole(empTable.getId(), ridList);

        return findEmpById(empTable.getId());
    }

    @Override
    public PageBean<Emp> findEmpList(String queryName, int roid, int currentPageNo, int pageSize) throws Exception {
        StringBuffer sql = new StringBuffer(" where e.name like '%");
        List<Object> params = new ArrayList<>();
        PageBean<Emp> customerPageDto;

        if (!StringUtils.isNullOrEmpty(queryName)) {
            sql.append(UtilString.trimAllSpace(queryName));
        }
        sql.append("%'");

        if (roid != 0) {
            sql.append(" and r.id = ?");
            sql = new StringBuffer("JOIN emp_role er ON e.id = er.e_id JOIN role r ON r.id = er.r_id").append(sql);
            params.add(roid);
        }
        System.out.println(new StringBuffer("SELECT * FROM emp e ").append(sql));
        customerPageDto = baseDao.getCustomerPageDto(new StringBuffer("SELECT * FROM emp e ").append(sql).toString(), params.toArray(), new BeanPropertyRowMapper<Emp>(Emp.class), new StringBuffer("select count(*) from emp e ").append(sql).toString(), params.toArray(), currentPageNo, pageSize);
        System.out.println(new StringBuffer("select *, r.id rid\n" +
                "from role r\n" +
                "         join emp_role er on r.id = er.r_id\n" +
                "         join emp e on e.id = er.e_id ").append(sql));
        List<Role> roleList = jdbcTemplate.query(
                " select *, r.id id, e.id empId\n" +
                        "from role r,\n" +
                        "     emp_role er,\n" +
                        "     emp e\n" +
                        "where r.id = er.r_id\n" +
                        "  and e.id = er.e_id ",
                new BeanPropertyRowMapper<Role>(Role.class));
        // 去重
        roleList = new ArrayList<>(new HashSet<Role>(roleList));

        List<Emp> empList = customerPageDto.getContent();
        for (int i = 0; i < empList.size(); i++) {
            List<Role> roles = new ArrayList<>();
            for (Role role : roleList) {
                if (empList.get(i).getId().equals(role.getEmpId())) {
                    roles.add(role);
                }
            }
            empList.get(i).setRoleList(roles);
        }
        return customerPageDto;
    }

    @Override
    public int insertEmpAndRole(int eid, List<Integer> ridList) throws Exception {
        String insertSql = "insert into emp_role(e_id, r_id) values (?, ?)";
        int insertRow = 0;

        for (Integer rid : ridList) {
            insertRow += jdbcTemplate.update(insertSql, eid, rid);
        }

        return insertRow;
    }

    @Override
    public int deleteEmpAndRole(int eid) throws Exception {
        String deleteSql = " delete from emp_role where e_id = ? ";

        return jdbcTemplate.update(deleteSql, eid);
    }
}
